from typing import List, Optional, Dict, Any
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_, or_, func, delete
from sqlalchemy.orm import selectinload

from app.models.user import Role, User, sys_user_role, sys_role_menu
from app.models.menu import Menu
from app.services.base import BaseService


class RoleService(BaseService[Role]):
    """角色服务"""

    def __init__(self):
        super().__init__(Role)

    async def get_roles_with_pagination(
        self, db: AsyncSession, *, tenant_id: str, page: int = 1, page_size: int = 10,
        filters: dict = None
    ) -> tuple:
        """分页获取角色列表"""
        # 构建基础查询
        query = select(Role).where(
            and_(
                Role.tenant_id == tenant_id,
                Role.del_flag == '0'
            )
        )

        # 应用过滤条件
        if filters:
            if filters.get('keyword'):
                keyword = f"%{filters['keyword']}%"
                query = query.where(
                    or_(
                        Role.name.like(keyword),
                        Role.code.like(keyword)
                    )
                )

            if filters.get('status') is not None:
                query = query.where(Role.status == filters['status'])

        # 获取总数
        count_query = select(func.count(Role.id)).where(
            and_(
                Role.tenant_id == tenant_id,
                Role.del_flag == '0'
            )
        )

        # 对count查询也应用过滤条件
        if filters:
            if filters.get('keyword'):
                keyword = f"%{filters['keyword']}%"
                count_query = count_query.where(
                    or_(
                        Role.name.like(keyword),
                        Role.code.like(keyword)
                    )
                )

            if filters.get('status') is not None:
                count_query = count_query.where(Role.status == filters['status'])

        total_result = await db.execute(count_query)
        total = total_result.scalar()

        # 分页查询
        offset = (page - 1) * page_size
        query = query.offset(offset).limit(page_size).order_by(Role.sort.asc(), Role.created_at.desc())

        result = await db.execute(query)
        roles = result.scalars().all()

        # 获取每个角色的用户数量
        role_list = []
        for role in roles:
            # 查询使用此角色的用户数量
            user_count_query = select(func.count(user_role_association.c.user_id)).where(
                and_(
                    user_role_association.c.role_id == role.id,
                    user_role_association.c.tenant_id == tenant_id
                )
            )
            user_count_result = await db.execute(user_count_query)
            user_count = user_count_result.scalar() or 0

            role_dict = {
                "id": role.id,
                "name": role.name,
                "code": role.code,
                "sort": role.sort,
                "status": role.status,
                "remark": role.remark,
                "user_count": user_count,
                "created_at": role.created_at.isoformat() if role.created_at else None,
                "updated_at": role.updated_at.isoformat() if role.updated_at else None,
                "tenant_id": role.tenant_id
            }
            role_list.append(role_dict)

        return role_list, total

    async def get_role_list(
        self, db: AsyncSession, *, tenant_id: str
    ) -> List[dict]:
        """获取角色列表（简单版本）"""
        query = select(Role).where(
            and_(
                Role.tenant_id == tenant_id,
                Role.del_flag == '0',
                Role.status == True
            )
        ).order_by(Role.sort.asc())

        result = await db.execute(query)
        roles = result.scalars().all()

        return [
            {
                "id": role.id,
                "name": role.name,
                "code": role.code,
                "sort": role.sort,
                "remark": role.remark
            }
            for role in roles
        ]

    async def create_role(
        self, db: AsyncSession, *, obj_in: Dict[str, Any], tenant_id: str
    ) -> Role:
        """创建角色"""
        # 提取权限ID
        permission_ids = obj_in.pop("permission_ids", [])

        # 创建角色
        role = await self.create(db, obj_in=obj_in, tenant_id=tenant_id)

        # 分配权限（如果有）
        if permission_ids:
            await self.assign_permissions(db, role_id=role.id, permission_ids=permission_ids, tenant_id=tenant_id)

        return role

    async def update_role(
        self, db: AsyncSession, *, db_obj: Role, obj_in: Dict[str, Any]
    ) -> Role:
        """更新角色"""
        # 提取权限ID
        permission_ids = obj_in.pop("permission_ids", None)

        # 更新角色基本信息
        role = await self.update(db, db_obj=db_obj, obj_in=obj_in)

        # 更新权限（如果提供了权限ID）
        if permission_ids is not None:
            await self.assign_permissions(db, role_id=role.id, permission_ids=permission_ids, tenant_id=role.tenant_id)

        return role

    async def delete_role(
        self, db: AsyncSession, *, role_id: int, tenant_id: str
    ) -> bool:
        """删除角色"""
        # 检查是否有用户使用此角色
        user_count_query = select(func.count(user_role_association.c.user_id)).where(
            and_(
                user_role_association.c.role_id == role_id,
                user_role_association.c.tenant_id == tenant_id
            )
        )
        user_count_result = await db.execute(user_count_query)
        user_count = user_count_result.scalar() or 0

        if user_count > 0:
            raise ValueError(f"该角色已被 {user_count} 个用户使用，无法删除")

        # 软删除角色
        return await self.delete(db, id=role_id)

    async def assign_permissions(
        self, db: AsyncSession, *, role_id: int, permission_ids: List[int], tenant_id: str
    ):
        """为角色分配权限"""
        # 删除现有权限关联
        await db.execute(
            delete(role_permission_association).where(
                and_(
                    role_permission_association.c.role_id == role_id,
                    role_permission_association.c.tenant_id == tenant_id
                )
            )
        )

        # 添加新权限关联
        if permission_ids:
            values = [
                {"role_id": role_id, "permission_id": permission_id, "tenant_id": tenant_id}
                for permission_id in permission_ids
            ]
            await db.execute(role_permission_association.insert().values(values))

        await db.commit()

    async def get_role_permissions(
        self, db: AsyncSession, *, role_id: int, tenant_id: str
    ) -> List[dict]:
        """获取角色的权限列表"""
        from app.models.user import Permission

        query = select(Permission).join(
            role_permission_association,
            Permission.id == role_permission_association.c.permission_id
        ).where(
            and_(
                role_permission_association.c.role_id == role_id,
                role_permission_association.c.tenant_id == tenant_id,
                Permission.is_deleted == False
            )
        ).order_by(Permission.sort.asc())

        result = await db.execute(query)
        permissions = result.scalars().all()

        return [
            {
                "id": perm.id,
                "name": perm.name,
                "code": perm.code,
                "type": perm.type,
                "path": perm.path,
                "component": perm.component,
                "perms": perm.perms,
                "icon": perm.icon,
                "sort": perm.sort
            }
            for perm in permissions
        ]

    async def get_role_users(
        self, db: AsyncSession, *, role_id: int, tenant_id: str
    ) -> List[dict]:
        """获取拥有此角色的用户列表"""
        query = select(User).join(
            user_role_association,
            User.id == user_role_association.c.user_id
        ).where(
            and_(
                user_role_association.c.role_id == role_id,
                user_role_association.c.tenant_id == tenant_id,
                User.is_deleted == False
            )
        ).order_by(User.created_at.desc())

        result = await db.execute(query)
        users = result.scalars().all()

        return [
            {
                "id": user.id,
                "username": user.username,
                "nickname": user.nickname,
                "email": user.email,
                "status": user.status,
                "created_at": user.created_at.isoformat() if user.created_at else None
            }
            for user in users
        ]

    async def get_roles_by_ids(
        self, db: AsyncSession, *, role_ids: List[int], tenant_id: str
    ) -> List[Role]:
        """根据ID列表获取角色"""
        query = select(Role).where(
            and_(
                Role.id.in_(role_ids),
                Role.tenant_id == tenant_id,
                Role.del_flag == '0'
            )
        )

        result = await db.execute(query)
        return result.scalars().all()


# 创建角色服务实例
role_service = RoleService()